import mysql.connector
from mysql.connector import Error

class DatabaseManager:
    def __init__(self):
        self.connection = None
        self.db_name = 'gomoku_db'
        
    def connect(self):
        """连接到MySQL数据库"""
        try:
            self.connection = mysql.connector.connect(
                host="localhost",
                user="root",
                password="123456",
                database="gomoku_db"
            )
            if self.connection.is_connected():
                print("数据库连接成功！")
                return True
            else:
                print("数据库连接失败（未激活连接）")
            return False
        except Error as e:
            print(f"数据库连接失败: {e}")
            return False
    
    def close(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            print("数据库连接已关闭")
            
    def execute_query(self, query, params=None):
        """执行查询语句"""
        try:
            # 每次查询都创建新的连接，避免未读取结果的问题
            if not self.connect():
                print("无法连接到数据库")
                return None
                
            cursor = self.connection.cursor(buffered=True)  # 使用buffered=True避免未读取结果的问题
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
                
            self.connection.commit()
            return cursor
        except Error as e:
            print(f"执行查询时出错: {e}")
            if self.connection and self.connection.is_connected():
                self.connection.rollback()  # 发生错误时回滚
            return None
            
    def fetch_data(self, query, params=None):
        """执行查询并获取结果"""
        cursor = self.execute_query(query, params)
        if cursor:
            try:
                result = cursor.fetchall()
                cursor.close()
                return result
            except Error as e:
                print(f"获取数据时出错: {e}")
                cursor.close()
                return None
        return None
    
    def check_user_exists(self, username):
        """检查用户名是否已存在"""
        query = "SELECT COUNT(*) FROM users WHERE username = %s"
        result = self.fetch_data(query, (username,))
        if result and result[0][0] > 0:
            return True
        return False
    
    def verify_login(self, username, password):
        """验证用户登录"""
        print(f"尝试验证用户: {username}, 密码长度: {len(password) if password else 0}")
        
        # 处理null密码的特殊情况
        if password is None or password.strip() == "":
            query = "SELECT * FROM users WHERE username = %s AND (password IS NULL OR password = '')"
            params = (username,)
        else:
            query = "SELECT * FROM users WHERE username = %s AND password = %s"
            params = (username, password)
            
        print(f"执行查询: {query}")
        
        result = self.fetch_data(query, params)
        print(f"查询结果: {result}")
        
        if result and len(result) > 0:
            print("验证成功")
            return True
        
        print("验证失败")
        return False
    
    def register_user(self, username, password):
        """注册新用户"""
        if self.check_user_exists(username):
            return False, "用户名已存在"
        
        query = "INSERT INTO users (username, password) VALUES (%s, %s)"
        cursor = self.execute_query(query, (username, password))
        
        if cursor:
            cursor.close()
            # 同时为新用户创建排行榜记录
            self.init_ranking(username)
            return True, "注册成功"
        return False, "注册失败"
    

            
    def init_ranking(self, username):
        """为用户初始化排行榜记录"""
        try:
            # 检查用户是否已有排行榜记录
            query = "SELECT COUNT(*) FROM ranking WHERE username = %s"
            result = self.fetch_data(query, (username,))
            
            if result and result[0][0] == 0:
                # 如果没有记录，创建一个新记录
                query = "INSERT INTO ranking (username, wins, losses, draws, score) VALUES (%s, 0, 0, 0, 0)"
                cursor = self.execute_query(query, (username,))
                if cursor:
                    cursor.close()
                    print(f"为用户 {username} 创建排行榜记录")
                    return True
            return False
        except Error as e:
            print(f"初始化排行榜记录时出错: {e}")
            return False
            
    def update_ranking(self, username, result):
        """更新用户的排行榜记录
        result: 'win', 'loss', 或 'draw'
        """
        try:
            print(f"\n==== 数据库更新排行榜 - 用户: {username}, 结果: {result} ====")
            
            # 首先确保用户有排行榜记录
            print(f"确保用户 {username} 有排行榜记录...")
            self.init_ranking(username)
            
            # 查询当前记录
            print(f"查询用户 {username} 当前排行榜记录...")
            query_current = "SELECT * FROM ranking WHERE username = %s"
            current_data = self.fetch_data(query_current, (username,))
            print(f"当前排行榜记录: {current_data}")
            
            # 根据比赛结果更新记录
            if result == 'win':
                query = """
                    UPDATE ranking 
                    SET wins = wins + 1, score = score + 3 
                    WHERE username = %s
                """
                print("执行胜利更新: wins+1, score+3")
            elif result == 'loss':
                query = """
                    UPDATE ranking 
                    SET losses = losses + 1 
                    WHERE username = %s
                """
                print("执行失败更新: losses+1")
            elif result == 'draw':
                query = """
                    UPDATE ranking 
                    SET draws = draws + 1, score = score + 1 
                    WHERE username = %s
                """
                print("执行平局更新: draws+1, score+1")
            else:
                print(f"无效的比赛结果: {result}")
                return False
                
            print(f"执行更新查询: {query} 参数: {username}")
            cursor = self.execute_query(query, (username,))
            if cursor:
                affected_rows = cursor.rowcount
                cursor.close()
                print(f"已更新用户 {username} 的排行榜记录: {result}, 影响行数: {affected_rows}")
                
                # 验证更新结果
                print("验证更新结果...")
                verify_query = "SELECT * FROM ranking WHERE username = %s"
                verify_data = self.fetch_data(verify_query, (username,))
                print(f"更新后的排行榜记录: {verify_data}")
                
                return True
            return False
        except Error as e:
            print(f"更新排行榜记录时出错: {e}")
            import traceback
            traceback.print_exc()
            return False
            
    def get_ranking(self, limit=10):
        """获取排行榜数据，按得分降序排列"""
        query = """
            SELECT username, wins, losses, draws, score 
            FROM ranking 
            ORDER BY score DESC 
            LIMIT %s
        """
        result = self.fetch_data(query, (limit,))
        return result

